<?php
require_once "../ThinkPHP/Library/Vendor/Excel/PHPExcel.php";;

$postData = $_REQUEST['data'];
$dataArray = json_decode($postData,true);

header("Content-type: text/html; charset=utf-8");
error_reporting(E_ALL || ~E_NOTICE);
$sqlserver  = '192.168.1.10';
$sqldatabase= 'GT';
$sqluser    = 'sa2';
$sqlpassword= 'sasa';
$sqlconnection = odbc_connect("Driver={SQL Server};Server=$sqlserver;Database=$sqldatabase;", $sqluser, $sqlpassword);

    $orderby = 'order by  U_DocDate desc ,U_OrderId';
	$total   = 0;

	$wheres  = $dataArray['q'];
	$countsql= 'select count(1) as total from [@ti_z0081] as t LEFT JOIN [@ti_z0080] as z on t.DocEntry=z.DocEntry where 1=1 ';
	$sqlquery = '';
	foreach($wheres as $key=>$where){
		if($where){
		    //检查条件是否为中文字符
		    if(cnCheck($where)){
		        $where = iconv("utf-8","gb2312", $where);
		        $sqlquery.= " and {$key} like '%{$where}%'  ";
		        $countsql.= " and {$key} like '%{$where}%'  ";
		    }else{
		        $where = iconv("utf-8","gb2312", $where);
		        $sqlquery.= " and {$key} = '{$where}'  ";
		        $countsql.= " and {$key} = '{$where}'  ";
		    }
			unset($where);
		}	
	}

	
	if($dataArray['udocdate']){
		$start = $dataArray['udocdate']['start'];
		$end  =  $dataArray['udocdate']['end'];
		$sqlquery.= "and z.U_DocDate between '{$start}' and '{$end}' ";  
	    $countsql.= "and z.U_DocDate between '{$start}' and '{$end}' ";  
	}
	
	if($dataArray['statuslist']){
		$statusStr= $dataArray['statuslist'];
		$sqlquery.= " AND U_OStatus in ({$statusStr}) ";
		$countsql.= " AND U_OStatus in ({$statusStr}) ";
	}


	$sqlquery="SELECT * FROM ( SELECT row_number () OVER (  {$orderby} ) id ,* FROM ( SELECT U_CardCode, t.U_Total AS total, t.* FROM [@ti_z0081] AS t LEFT JOIN [@ti_z0080] AS z ON t.DocEntry = z.DocEntry where 1=1 {$sqlquery} ) t1 ) t2 ";
	error_log($sqlquery);
	//查询总数量
	$total_res=odbc_exec($sqlconnection,$countsql);
	while(odbc_fetch_row($total_res)) {
	$total = odbc_result($total_res, 'total');
	}
	//数据查询
	$result=odbc_exec($sqlconnection,$sqlquery);
	//echo $sqlquery;
    while ($data[] = odbc_fetch_array($result));
	odbc_close($sqlconnection);
	//error_log(var_export($data,1));
	$keylist = array_keys($data[0]);
	//将结果转码utf-8
	foreach($data as $key=>$value){
	
		foreach($keylist as $keyname){
			
			$data[$key][$keyname]=iconv("gbk","utf-8//IGNORE",$value[$keyname]);
			
		}
	
		 $data[$key]['total']= sprintf("%.2f",  $data[$key]['total']);
		 $data[$key]['U_NoDPrc']= sprintf("%.2f",  $data[$key]['U_NoDPrc']);
		
		if($data[$key]['U_OTPhone']){
		$data[$key]['U_OTPhone']='<a href="sip:'.$data[$key]['U_OTPhone'].'" title="'.$data[$key]['U_Cstname'].'"  >'.$data[$key]['U_OTPhone'].'</a>';
		}
		//解决一条数据包含多个电话号码
		$temp_tell = explode(' ',$data[$key]['U_Phone']);
		$data[$key]['U_Phone']='';
		foreach($temp_tell as $t){
			if(count($temp_tell)>1){
				$data[$key]['U_Phone'].='<a href="sip:'.$t.'" title="'.$data[$key]['U_Cstname'].'"  >'.$t.'</a>'.'</br>';
			}else
			$data[$key]['U_Phone'].='<a href="sip:'.$t.'" title="'.$data[$key]['U_Cstname'].'"  >'.$t.'</a>';
		}				
	}
	   
    	 $count =count($data)-1;
    	unset($data[$count]);

$objPHPExcel = new PHPExcel();
$currentSheet = $objPHPExcel->getSheet(0);
$m = 1;
$currentSheet->setCellValue('A'.$m, '订单号');
$currentSheet->setCellValue('B'.$m, '当前订单状态');
$currentSheet->setCellValue('C'.$m, '订单优先级');
$currentSheet->setCellValue('D'.$m, '物料代码');
$currentSheet->setCellValue('E'.$m, '颜色');
$currentSheet->setCellValue('F'.$m, '物料描述');
$currentSheet->setCellValue('G'.$m, '收货人');
$currentSheet->setCellValue('H'.$m, '数量');
$currentSheet->setCellValue('I'.$m, '行类型');
$currentSheet->setCellValue('J'.$m, '结算价');
$currentSheet->setCellValue('K'.$m, '电话号码');
$currentSheet->setCellValue('L'.$m, '发票抬头');
$currentSheet->setCellValue('M'.$m, '地址');
$currentSheet->setCellValue('N'.$m, '备注');
$currentSheet->setCellValue('O'.$m, '订单确认日期');
$currentSheet->setCellValue('P'.$m, '已发货时间');
$currentSheet->setCellValue('Q'.$m, '是否代发');
$currentSheet->setCellValue('R'.$m, '发票类型');
$currentSheet->setCellValue('S'.$m, '是否拣配');
$currentSheet->setCellValue('T'.$m, '快递公司');
$currentSheet->setCellValue('U'.$m, '快递号');
$currentSheet->setCellValue('V'.$m, '发货序列号');
$currentSheet->setCellValue('W'.$m, '客户代码');
$currentSheet->setCellValue('X'.$m, '客户描述');
$currentSheet->setCellValue('Y'.$m, '产品经理');
$currentSheet->setCellValue('Z'.$m, '产品性质');

//
$currentSheet->setCellValue('AA'.$m, '买家姓名');
$currentSheet->setCellValue('AB'.$m, '售前订单号');
$currentSheet->setCellValue('AC'.$m, '售后订单号');
$currentSheet->setCellValue('AD'.$m, '客户物料号');
$currentSheet->setCellValue('AE'.$m, '客户物料名称');

$currentSheet->setCellValue('AF'.$m, '总计');
$currentSheet->setCellValue('AG'.$m, '单据日期');
$currentSheet->setCellValue('AH'.$m, '其他电话号码');
$currentSheet->setCellValue('AI'.$m, '导入单号');
$currentSheet->setCellValue('AJ'.$m, '导入单行');

$currentSheet->setCellValue('AK'.$m, '商品型号');
$currentSheet->setCellValue('AL'.$m, '省');
$currentSheet->setCellValue('AM'.$m, '市');
$currentSheet->setCellValue('AN'.$m, '县/区');
$currentSheet->setCellValue('AO'.$m, '订单取消原因');

$currentSheet->setCellValue('AP'.$m, '订单取消备注');
$currentSheet->setCellValue('AQ'.$m, '开票备注');
$currentSheet->setCellValue('AR'.$m, '快递费');
$currentSheet->setCellValue('AS'.$m, '核重');
$currentSheet->setCellValue('AT'.$m, '发票号');

$currentSheet->setCellValue('AU'.$m, '开票日期');
$currentSheet->setCellValue('AV'.$m, '财务是否开票');
$currentSheet->setCellValue('AW'.$m, '发票已寄出');
$currentSheet->setCellValue('AX'.$m, '仓库代码');

//$currentSheet->setCellValue('AY'.$m, '发货仓库');
//$currentSheet->setCellValue('AZ'.$m, '仓库名称');
//$currentSheet->setCellValue('BA'.$m, '发货仓库名称');
//$currentSheet->setCellValue('BB'.$m, '客服代码');
//$currentSheet->setCellValue('BC'.$m, '客服名称');
//$currentSheet->setCellValue('BD'.$m, '行状态');
//
//$currentSheet->setCellValue('BE'.$m, '已发签收短信时间');
//$currentSheet->setCellValue('BF'.$m, '确认收货时间');
//$currentSheet->setCellValue('BG'.$m, '已发货待取消时间');
//$currentSheet->setCellValue('BH'.$m, '丢件/损坏时间');
//$currentSheet->setCellValue('BI'.$m, '投诉时间');
//
//$currentSheet->setCellValue('BJ'.$m, '处理时间');
//$currentSheet->setCellValue('BK'.$m, '售后服务类型');
//$currentSheet->setCellValue('BL'.$m, '投诉原因');
//$currentSheet->setCellValue('BM'.$m, '投诉结果');
//$currentSheet->setCellValue('BN'.$m, '处理过程');
//
//$currentSheet->setCellValue('BO'.$m, '投诉咨询');

$cellIndex=2;
foreach ($data as $k => $v) {
    $currentSheet->setCellValueExplicit('A' . $cellIndex, $v['U_OrderId'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('B' . $cellIndex, $v['U_OStatus'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('C' . $cellIndex, $v['U_YXJ'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('D' . $cellIndex, $v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('E' . $cellIndex, $v['U_Color'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('F' . $cellIndex, $v['U_ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('G' . $cellIndex, $v['U_Cstname'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('H' . $cellIndex, $v['U_Qty'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('I' . $cellIndex, $v['U_Type'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('J' . $cellIndex, $v['U_NoDPrc'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('K' . $cellIndex, $v['U_Phone'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('L' . $cellIndex, $v['U_InHead'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('M' . $cellIndex, $v['U_Address'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('N' . $cellIndex, $v['U_Memo'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('O' . $cellIndex, $v['U_IdenDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('P' . $cellIndex, '', \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('Q' . $cellIndex, $v['U_Replace'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('R' . $cellIndex, $v['U_InType'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('S' . $cellIndex, $v['U_IsPick'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('T' . $cellIndex, $v['U_ExpCom'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('U' . $cellIndex, $v['U_ExpNo'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('V' . $cellIndex, $v['U_SerNum'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('W' . $cellIndex, $v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('X' . $cellIndex, $v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('Y' . $cellIndex, $v['U_PManager'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('Z' . $cellIndex, '', \PHPExcel_Cell_DataType::TYPE_STRING);

    $currentSheet->setCellValueExplicit('AA' . $cellIndex, $v['U_Cstname'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AB' . $cellIndex, '', \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AC' . $cellIndex, '', \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AD' . $cellIndex, $v['U_CitemCd'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AE' . $cellIndex, $v['U_CitemNm'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AF' . $cellIndex, '', \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AG' . $cellIndex, $v['U_DocDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AH' . $cellIndex, $v['U_OTPhone'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AI' . $cellIndex, $v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AJ' . $cellIndex, $v['LineId'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AK' . $cellIndex, $v['U_Model'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AL' . $cellIndex, $v['U_State'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AM' . $cellIndex, $v['U_City'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AN' . $cellIndex, $v['U_County'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AO' . $cellIndex, $v['U_IsClose'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AP' . $cellIndex, $v['U_ClsMemo'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AQ' . $cellIndex, $v['U_InvMemo'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AR' . $cellIndex, $v['U_ExpCost'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AS' . $cellIndex, '', \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AT' . $cellIndex, $v['U_InNo'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AU' . $cellIndex, $v['U_InDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AV' . $cellIndex, $v['U_IsKP'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AW' . $cellIndex, $v['U_IsInvD'], \PHPExcel_Cell_DataType::TYPE_STRING);
    $currentSheet->setCellValueExplicit('AX' . $cellIndex, $v['U_WhsCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

    $cellIndex ++;
}
$name = uniqid().'.xls';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $name );
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

    	exit;

	    /**
    	 * 检查是否为中文
    	 * @param unknown $str
    	 * @return boolean
    	 */
    	function cnCheck($str){
    	    if (preg_match("/[\x7f-\xff]/", $str)) {
    	       return  true;
    	    }else{
    	       return false;
    	    }
    	}